-- lookup the MapKey for the NETWORK_IO wait type
SELECT
	map_key, map_value
FROM sys.dm_xe_map_values
WHERE map_value = N'NETWORK_IO'
  AND name = N'wait_types';
GO

-- extended event session to monitor network waits
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'NetworkWaits_HostName')
	DROP EVENT SESSION NetworkWaits_HostName ON SERVER;
GO

-- create event session to track network waits by host
CREATE EVENT SESSION NetworkWaits_HostName ON SERVER 
ADD EVENT  sqlos.wait_info
(    --capture the scheduler_id
    ACTION(sqlos.scheduler_id, sqlserver.client_hostname)
    WHERE
        (opcode = 1 -- end events only
            AND duration > 0 AND (wait_type = 99) -- network waits
		)            
)
ADD TARGET package0.histogram(
	SET filtering_event_name = N'sqlos.wait_info',
		source_type = 1, -- Action
		source = N'sqlserver.client_hostname');
GO

-- create event session to track network waits by application
/*
CREATE EVENT SESSION TrackNetworkWaits_AppName ON SERVER 
ADD EVENT  sqlos.wait_info
(    -- capture the scheduler_id
    ACTION(sqlos.scheduler_id, sqlserver.client_app_name)
    WHERE
        (opcode = 1 -- end events only
            AND duration > 0 AND (wait_type = 99) -- network waits
		)            
)
ADD TARGET package0.histogram(
	SET filtering_event_name = N'sqlos.wait_info',
		source_type = 1, -- Action
		source = N'sqlserver.client_app_name');
GO
*/

-- start the event session
ALTER EVENT SESSION NetworkWaits_HostName ON SERVER STATE = START;
GO

-- monitor workload

-- query target data
SELECT 
    n.value('(value)[1]', 'nvarchar(4000)') AS [host_name],
    n.value('(@count)[1]', 'int') AS network_wait_count
FROM
(SELECT CAST(target_data as XML) target_data
 FROM sys.dm_xe_sessions AS s 
 JOIN sys.dm_xe_session_targets t
     ON s.address = t.event_session_address
 WHERE s.name = N'TrackNetworkWaits_HostName'
   AND t.target_name = N'histogram') as tab
CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n)
ORDER BY [host_name];
GO

-- stop the event session
ALTER EVENT SESSION NetworkWaits_HostName ON SERVER STATE = STOP;
GO

-- cleanup
DROP EVENT SESSION NetworkWaits_HostName ON SERVER;
GO